<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

function dt_list_top_games($offset, $limit) {
    $offset = mysql_real_escape_string($offset);
    $limit = mysql_real_escape_string($limit);
    $query = "SELECT game_id, COUNT(user_id) as ratings
                FROM draw_together_game_ratings
                GROUP BY game_id
                ORDER BY ratings DESC
                LIMIT $offset, $limit";
    return safe_select_query($query);
}

function dt_list_top_users($offset, $limit) {
    $offset = mysql_real_escape_string($offset);
    $limit = mysql_real_escape_string($limit);
    $queryn = "SELECT noun_id as user_id, SUM( ratings ) AS reputation
                FROM (

                    SELECT dtgs.noun_id, dtgs.adjective_id, dtgr.game_id, COUNT( dtgr.user_id ) AS ratings
                    FROM draw_together_game_ratings dtgr, draw_together_games dtgs
                    WHERE dtgr.game_id = dtgs.id
                    GROUP BY game_id
                    ) dtrs
                GROUP BY user_id
                ORDER BY reputation DESC
                LIMIT $offset, $limit";
    $resultn = safe_select_query($queryn);
    //var_dump($resultn);
    $querya = "SELECT adjective_id as user_id, SUM( ratings ) AS reputation
                FROM (

                    SELECT dtgs.noun_id, dtgs.adjective_id, dtgr.game_id, COUNT( dtgr.user_id ) AS ratings
                    FROM draw_together_game_ratings dtgr, draw_together_games dtgs
                    WHERE dtgr.game_id = dtgs.id
                    GROUP BY game_id
                    ) dtrs
                GROUP BY user_id
                ORDER BY reputation DESC
                LIMIT $offset, $limit";
    $resulta = safe_select_query($querya);
    //var_dump($resulta);
    $result = array();
    foreach ($resultn as $rn) {
        $result[$rn->user_id] += $rn->reputation;
    }
    foreach ($resulta as $ra) {
        $result[$ra->user_id] += $ra->reputation;
    }
    //var_dump($result);
    arsort($result);
    //var_dump($result);
    return $result;
}

function dt_calc_reputation($user_id) {
    $reputation = 0;
    $user_id = mysql_real_escape_string($user_id);
    $queryn = "SELECT noun_id as user_id, SUM( ratings ) AS reputation
                FROM (
                    SELECT dtgs.noun_id, dtgs.adjective_id, dtgr.game_id, COUNT( dtgr.user_id ) AS ratings
                    FROM draw_together_game_ratings dtgr, draw_together_games dtgs
                    WHERE dtgr.game_id = dtgs.id
                    AND (dtgs.noun_id = '$user_id' OR dtgs.adjective_id = '$user_id')
                    GROUP BY game_id
                    ) dtrs
                WHERE noun_id = '$user_id'
                GROUP BY noun_id";
    $resultn = safe_select_query($queryn);
    if($resultn && $resultn[0]) {
        $reputation += $resultn[0]->reputation;
    }
    //var_dump($resultn);
    $querya = "SELECT adjective_id as user_id, SUM( ratings ) AS reputation
                FROM (

                    SELECT dtgs.noun_id, dtgs.adjective_id, dtgr.game_id, COUNT( dtgr.user_id ) AS ratings
                    FROM draw_together_game_ratings dtgr, draw_together_games dtgs
                    WHERE dtgr.game_id = dtgs.id
                    AND (dtgs.noun_id = '$user_id' OR dtgs.adjective_id = '$user_id')
                    GROUP BY game_id
                    ) dtrs
                WHERE adjective_id = '$user_id'
                GROUP BY adjective_id";
    $resulta = safe_select_query($querya);
    if($resulta && $resulta[0]) {
        $reputation += $resulta[0]->reputation;
    }
    return $reputation;
}

?>
